/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

/**
 *
 * @author Trang
 */
public class IssuingDAO {
    private Connection con;
    public IssuingDAO()
    {
         con = DBFactory.createConnection("onlineshopping");
    }
    public boolean InsertIssue(Issue issue)
    {
        boolean result = false;

        try {
            //insert member account 1st
            //07/01/2013
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
            Date current = new Date();
            String sqlst1 = "INSERT INTO issue(createTime, Order_idOrder, Staff_idStaff) "
                    + "VALUE(?, ?, ?)";
            
            PreparedStatement ps = con.prepareStatement(sqlst1);
            if(issue.getCreateTime() == null)
                ps.setString(1,dateFormat.format(current));
            else 
                ps.setString(1,dateFormat.format(issue.getCreateTime()));
            ps.setInt(2, issue.getOrder().getIdOrder());
            ps.setInt(3, issue.getStaff().getIdStaff());  
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public boolean InsertIssue(Integer idOrder, Integer idStaff, Date createDate)
    {
        boolean result = false;

        try {
            //insert member account 1st
            
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            String sqlst1 = "INSERT INTO issue(createTime, Order_idOrder, Staff_idStaff) "
                    + "VALUE(?, ?, ?)";
            
            PreparedStatement ps = con.prepareStatement(sqlst1);
            if(createDate == null)
                ps.setString(1,dateFormat.format(current));
            else 
                ps.setString(1,dateFormat.format(createDate));
            ps.setInt(2, idOrder);
            ps.setInt(3, idStaff);  
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public List<Issue> getIssueByDate(Date time)
    {
        List<Issue> results = new ArrayList<Issue>();
        try
        {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String newDate = dateFormat.format(time);
                String sql ="SELECT * FROM issue WHERE createTime LIKE'"+newDate+"%'";
                 PreparedStatement ps = con.prepareStatement(sql);
                 System.out.println(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer idIssue = rs.getInt("idIssue");
                Date createTime = rs.getDate("createTime"); 
                Integer Order_idOrder = rs.getInt("Order_idOrder");
                Integer Staff_idStaff = rs.getInt("Staff_idStaff");
                
                Order order = new OrderDAO().getOrderbyID(Order_idOrder);
                
                Staff staff = new StaffDAO().getStaffByStaffId(Staff_idStaff);
                
                Issue issue = new Issue(createTime, order, staff);
                
                results.add(issue);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
    
    
     public List<Issue> getIssueByDate(String time)
    {
        List<Issue> results = new ArrayList<Issue>();
        try
        {
//            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
//            String newDate = dateFormat.format(time);
            //System.out.println(newDate);
            String sql ="SELECT * FROM issue WHERE createTime LIKE'%"+time+"%'";
            System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer idIssue = rs.getInt("idIssue");
                Date createTime = rs.getDate("createTime"); 
                Integer Order_idOrder = rs.getInt("Order_idOrder");
                Integer Staff_idStaff = rs.getInt("Staff_idStaff");
                
                Order order = new OrderDAO().getOrderbyID(Order_idOrder);
                
                Staff staff = new StaffDAO().getStaffByStaffId(Staff_idStaff);
                
                Issue issue = new Issue(createTime, order, staff);
                
                results.add(issue);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
     
      public List<Issue> getAllIssue()
    {
        List<Issue> results = new ArrayList<Issue>();
        try
        {
            String sql ="SELECT * FROM issue ORDER By createTime DESC ";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer idIssue = rs.getInt("idIssue");
                Date createTime = rs.getDate("createTime"); 
                Integer Order_idOrder = rs.getInt("Order_idOrder");
                Integer Staff_idStaff = rs.getInt("Staff_idStaff");
                
                Order order = new OrderDAO().getOrderbyID(Order_idOrder);
                
                Staff staff = new StaffDAO().getStaffByStaffId(Staff_idStaff);
                System.out.println(staff.getFirstname());
                Issue issue = new Issue(createTime, order, staff);
                
                results.add(issue);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
      
    public List<Issue> getIssueBetweenDate(Date starttime, Date endtime)
    {
        List<Issue> results = new ArrayList<Issue>();
        try
        {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String date1 = dateFormat.format(starttime);
            String date2 = dateFormat.format(endtime);
            //System.out.println(newDate);
            String sql ="SELECT * FROM issue WHERE createTime BETWEEN ? AND ?";
            System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date (starttime.getTime()));
            ps.setDate(2, new java.sql.Date (endtime.getTime()));
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer idIssue = rs.getInt("idIssue");
                Date createTime = rs.getDate("createTime"); 
                Integer Order_idOrder = rs.getInt("Order_idOrder");
                Integer Staff_idStaff = rs.getInt("Staff_idStaff");
                
                Order order = new OrderDAO().getOrderbyID(Order_idOrder);
                
                Staff staff = new StaffDAO().getStaffByStaffId(Staff_idStaff);
                
                Issue issue = new Issue(createTime, order, staff);
                
                results.add(issue);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
//    public List<Orderitem> getOrderitemByOrderId(Integer idOrder)
//    {
//        List<Orderitem> results = new ArrayList<Orderitem>();    
//        try
//        {
//            String sql = "SELECT * FROM orderitem WHERE Order_idOrder= ?";
//            PreparedStatement ps = con.prepareStatement(sql);
//            ps.setInt(1, idOrder);
//            ResultSet rs = ps.executeQuery();
//            while(rs.next())
//            {
//                Integer orderQuantity = rs.getInt("orderQuantity");
//                double orderPrice = rs.getDouble("orderPrice");
//                Integer Product_idProduct = rs.getInt("Product_idProduct");
//                
//            }
//        }catch (SQLException ex) {
//            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
//        }    
//        return results;
//    }
     
}
